package org.dromara.Nyt7PolicyInsurance.mapper;

import org.apache.ibatis.annotations.Select;
import org.dromara.Nyt7PolicyInsurance.domain.Nyt7PolicyInsurance;
import org.dromara.Nyt7PolicyInsurance.domain.vo.Nyt7PolicyInsuranceSearchVo;
import org.dromara.Nyt7PolicyInsurance.domain.vo.Nyt7PolicyInsuranceVo;
import org.dromara.common.mybatis.core.mapper.BaseMapperPlus;

import java.util.List;

/**
 * 保单查询Mapper接口
 *
 * @author Lion Li
 * @date 2025-01-09
 */
public interface Nyt7PolicyInsuranceMapper extends BaseMapperPlus<Nyt7PolicyInsurance, Nyt7PolicyInsuranceVo> {
    @Select("""
        SELECT
            p.*,
            sp.post_name AS 'sys_post.post_name',
            f.farmer_name AS 'farmer_insurance_info.farmer_name',
            f.farmer_id_card AS 'farmer_insurance_info.farmer_id_card',
            f.plot_count AS 'farmer_insurance_info.plot_count',
            f.measured_area AS 'farmer_insurance_info.measured_area',
            f.insured_area AS 'farmer_insurance_info.insured_area',
            pd.city AS  'plot_detail_info.city',
            pd.district_county AS  'plot_detail_info.district_county',
            pd.plot_code AS 'plot_detail_info.plot_code',
            pd.insured_area AS 'plot_detail_info.insured_area',
            pd.measured_area AS 'plot_detail_info.measured_area',
            pd.confirmation_status AS 'plot_detail_info.confirmation_status',
            pd.latitude AS 'plot_detail_info.latitude',
            pd.longitude AS 'plot_detail_info.longitude',
            pd.town AS 'plot_detail_info.town',
            pd.village AS 'plot_detail_info.village'
        FROM
            nyt_7_policy_insurance p
        LEFT JOIN
            sys_post sp ON p.post_id = sp.post_id
        LEFT JOIN
            nyt_7_farmer_insurance_info f ON p.farmer_id = f.id
        LEFT JOIN
            nyt_7_plot_detail_info pd ON p.plot_id = pd.id
            where p.del_flag = '0'""")
    List<Nyt7PolicyInsurance> selectEx();

    @Select("""
        SELECT
            p.*,
            sp.post_name AS 'sys_post.post_name',
            f.farmer_name AS 'farmer_insurance_info.farmer_name',
            f.farmer_id_card AS 'farmer_insurance_info.farmer_id_card',
            f.plot_count AS 'farmer_insurance_info.plot_count',
            f.measured_area AS 'farmer_insurance_info.measured_area',
            f.insured_area AS 'farmer_insurance_info.insured_area',
            pd.city AS  'plot_detail_info.city',
            pd.district_county AS  'plot_detail_info.district_county',
            pd.plot_code AS 'plot_detail_info.plot_code',
            pd.insured_area AS 'plot_detail_info.insured_area',
            pd.measured_area AS 'plot_detail_info.measured_area',
            pd.confirmation_status AS 'plot_detail_info.confirmation_status',
            pd.latitude AS 'plot_detail_info.latitude',
            pd.longitude AS 'plot_detail_info.longitude',
            pd.town AS 'plot_detail_info.town',
            pd.village AS 'plot_detail_info.village'
        FROM
            nyt_7_policy_insurance p
        LEFT JOIN
            sys_post sp ON p.post_id = sp.post_id
        LEFT JOIN
            nyt_7_farmer_insurance_info f ON p.farmer_id = f.id
        LEFT JOIN
            nyt_7_plot_detail_info pd ON p.plot_id = pd.id
        WHERE p.id = #{id} and p.del_flag = '0'""")
    Nyt7PolicyInsuranceVo selectVoByIdEx(Long id);

    @Select("""
        SELECT
            p.*,
            sp.post_name AS 'sys_post.post_name',
            f.farmer_name AS 'farmer_insurance_info.farmer_name',
            f.farmer_id_card AS 'farmer_insurance_info.farmer_id_card',
            f.plot_count AS 'farmer_insurance_info.plot_count',
            f.measured_area AS 'farmer_insurance_info.measured_area',
            f.insured_area AS 'farmer_insurance_info.insured_area',
            pd.city AS  'plot_detail_info.city',
            pd.district_county AS  'plot_detail_info.district_county',
            pd.plot_code AS 'plot_detail_info.plot_code',
            pd.insured_area AS 'plot_detail_info.insured_area',
            pd.measured_area AS 'plot_detail_info.measured_area',
            pd.confirmation_status AS 'plot_detail_info.confirmation_status',
            pd.latitude AS 'plot_detail_info.latitude',
            pd.longitude AS 'plot_detail_info.longitude',
            pd.town AS 'plot_detail_info.town',
            pd.village AS 'plot_detail_info.village'
        FROM
            nyt_7_policy_insurance p
        LEFT JOIN
            sys_post sp ON p.post_id = sp.post_id
        LEFT JOIN
            nyt_7_farmer_insurance_info f ON p.farmer_id = f.id
        LEFT JOIN
            nyt_7_plot_detail_info pd ON p.plot_id = pd.id
         WHERE sp.post_name LIKE #{postName} and p.del_flag = '0'""")
    List<Nyt7PolicyInsurance> selectByPostNameEx(String postName);

    @Select("""
        SELECT
            p.*,
            sp.post_name AS 'sys_post.post_name',
            f.farmer_name AS 'farmer_insurance_info.farmer_name',
            f.farmer_id_card AS 'farmer_insurance_info.farmer_id_card',
            f.plot_count AS 'farmer_insurance_info.plot_count',
            f.measured_area AS 'farmer_insurance_info.measured_area',
            f.insured_area AS 'farmer_insurance_info.insured_area',
            pd.city AS  'plot_detail_info.city',
            pd.district_county AS  'plot_detail_info.district_county',
            pd.plot_code AS 'plot_detail_info.plot_code',
            pd.insured_area AS 'plot_detail_info.insured_area',
            pd.measured_area AS 'plot_detail_info.measured_area',
            pd.confirmation_status AS 'plot_detail_info.confirmation_status',
            pd.latitude AS 'plot_detail_info.latitude',
            pd.longitude AS 'plot_detail_info.longitude',
            pd.town AS 'plot_detail_info.town',
            pd.village AS 'plot_detail_info.village'
        FROM
            nyt_7_policy_insurance p
        LEFT JOIN
            sys_post sp ON p.post_id = sp.post_id
        LEFT JOIN
            nyt_7_farmer_insurance_info f ON p.farmer_id = f.id
        LEFT JOIN
            nyt_7_plot_detail_info pd ON p.plot_id = pd.id
         WHERE pd.city LIKE #{city} and p.del_flag = '0'""")
    List<Nyt7PolicyInsurance> selectByCityEx(String city);

    @Select("SELECT f.id AS 'farmer_insurance_info.id', " +
        "f.farmer_name AS 'farmer_insurance_info.farmer_name', " +
        "f.farmer_id_card AS 'farmer_insurance_info.farmer_id_card', " +
        "f.family_member_name AS 'farmer_insurance_info.family_member_name' " +
        "FROM nyt_7_farmer_insurance_info f " +
        "where f.farmer_name LIKE concat('%',#{str}, '%') AND f.del_flag = '0' LIMIT 5")
    List<Nyt7PolicyInsuranceSearchVo> searchEntityFarmer(String str);

    @Select("SELECT p.id AS 'plot_detail_info.id', " +
        "p.plot_code AS 'plot_detail_info.plot_code', " +
        "p.city AS 'plot_detail_info.city', " +
        "f.farmer_name AS 'plot_detail_info.farmer_insurance_info.farmer_name' " +
        "FROM nyt_7_plot_detail_info p " +
        "        LEFT JOIN" +
        "            nyt_7_farmer_insurance_info f ON p.farmer_id = f.id " +
        "where p.plot_code LIKE concat('%',#{str}, '%') AND p.del_flag = '0' LIMIT 5")
    List<Nyt7PolicyInsuranceSearchVo> searchEntityPlot(String str);
}
